Partitioning fact tables in databases implemented using multiple related tables, such as a star schema configuration (e.g., a database including one or more fact tables, where each fact table has a plurality of associated dimension tables), can be problematic. For instance, dimension tables in certain star schema implementations are not partitioned. Accordingly, in order to properly process a query based on a dimension value (e.g., product name) included in a given dimension table (e.g., a product dimension table) of the database, the given dimension table must be joined with each of the corresponding fact table partitions. For large dimension tables, this may require large volumes of data (e.g., from the given dimension table) to be sent to each of the remote hosts (of the database) on which partitions of the corresponding fact table are located, where only a portion of the dimension table may be needed to properly execute a corresponding join operation.
In other implementations, fact table partitions can be defined based on a single dimension of a given star schema. For instance, in a star schema that includes a product dimension table (e.g., including product names and product groups) and a sales document dimension table (e.g., including an indexed listing of sales personnel), an associated fact table can be partitioned based on the product names (which can be included in a single column of the product dimension table), such as by using respective modulo results from hash values of the product names to determine which partition of the fact table a given record should be stored in. Such approaches can reduce the amount of data (from the product dimension table) that needs to be sent to each of the remote hosts on which partitions of the fact table are stored. However, in such approaches, all data (e.g., large amounts of data) of other dimension tables of the star schema may still need to be sent to each remote host (e.g., to facilitate outer join operations), which can still result in a large volume of data being sent over an associated network.
In still other implementations, partitioning of a fact table may be accomplished using a hierarchical, multi-level approach where a first dimension is used to determine a partition in which to store a record (or where a record is stored) and a second dimension is used to determine a sub-partition for the record. Depending on the specific hierarchical, multi-level partitioning implementation, the sub-partitions of a given partition (e.g., top-level partition) can be stored on a single host, or can be spread over multiple hosts. Such hierarchical, multi-level approaches, however, can violate architectural constraints of a number of database implementations. For instance, such approaches may not be feasible in database architectures that require all sub-partitions of a given (top-level) partition to be stored on a single host. Such constraints can result in local unique constraint checks. Further, some database architectures may be limited to two levels of partitioning, and one level may already be used for other purposes.